﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using TournamentDatabase.Objects;

namespace TournamentDatabase.Database
{
    class OracleQuerryParser:IOracleAccessor
    {
        DBConnection con;
        public OracleQuerryParser()
        {
            con = new DBConnection();
            con.Connect();
        }
        public string SelectClause(string[] selectType)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("SELECT ");

            sb.Append(selectType[0]);
            for (int i = 1; i < selectType.Length; i++)
            {
                sb.Append("||','||");
                sb.Append(selectType[i]);
            }
            return sb.ToString();
        }
        public string OrderClause(OrderBy order, string orderColumn)
        {
            if (order != OrderBy.None)
            {
                string st =" ORDER BY " +orderColumn;
                if (order == OrderBy.Descending)
                {
                    st = st + "DESC";
                }
                return st;
            }
            return "";
        }

        public string[] member = new string[] { "m.mid", "m.name", "m.address", "m.contactDetails" };
        public string[] competitor = new string[] { "m.mid", "m.name", "m.address", "m.contactDetails", "c.gender", "c.birthDate", "c.rankTitle", "c.arenaName" };
        public string[] competesIn = new string[] { "m.mid", "m.name", "m.address", "m.contactDetails", "c.gender", "c.birthDate", "c.rankTitle", "c.arenaName", "ci.placing", "ci.details" };
        public string[] judge = new string[] { "m.mid", "m.name", "m.address", "m.contactDetails", "j.qualification" };
        public string[] judgesIn = new string[] { "m.mid", "m.name", "m.address", "m.contactDetails", "j.qualification", "ji.role" };
        public string[] organiser = new string[] { "m.mid", "m.name", "m.address", "m.contactDetails", "o.authority" };
        public string[] organises = new string[] { "m.mid", "m.name", "m.address", "m.contactDetails", "o.authority", "oi.role" };
        public string[] tournament = new string[] { "t.tid", "t.startDate", "t.endDate", "t.title", "t.address" };
        public string[] competition = new string[] { "t.tid", "t.startDate", "t.endDate", "t.title", "t.address", "cp.type", "cp.minBirthDate", "cp.maxBirthDate", "cp.gender", "cp.startDateTime" };


        public List<Member> QueryMember(string whereClause, OrderBy order, string orderColumn)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append(SelectClause(member));
            sb.Append("From Member m");
            if (whereClause != "")
            {
                sb.Append(" Where ");
                sb.Append(whereClause);
            }
            sb.Append(OrderClause(order, orderColumn));

            return Member.ParseMember(con.MakeQuery(sb.ToString(), member));
        }

        public List<Competitor> QueryCompetitor(string whereClause, OrderBy order, string orderColumn)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append(SelectClause(competitor));
            sb.Append("From Member m, Competitor c");
            sb.Append(" Where m.mid = c.mid");
            if (whereClause != "")
            {
                sb.Append(" AND ");
                sb.Append(whereClause);
            }
            sb.Append(OrderClause(order, orderColumn));

            return Competitor.ParseCompetitor(con.MakeQuery(sb.ToString(), competitor));
        }

        public List<Judge> QueryJudge(string whereClause, OrderBy order, string orderColumn)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append(SelectClause(judge));
            sb.Append("From Member m, Judge j");
            sb.Append(" Where m.mid = j.mid");
            if (whereClause != "")
            {
                sb.Append(" AND ");
                sb.Append(whereClause);
            }
            sb.Append(OrderClause(order, orderColumn));

            return Judge.ParseJudge(con.MakeQuery(sb.ToString(), judge));
        }

        public List<Organiser> QueryOrganiser(string whereClause, OrderBy order, string orderColumn)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append(SelectClause(organiser));
            sb.Append("From Member m, Organiser o");
            sb.Append(" Where m.mid = o.mid");
            if (whereClause != "")
            {
                sb.Append(" AND ");
                sb.Append(whereClause);
            }
            sb.Append(OrderClause(order, orderColumn));

            return Organiser.ParseOrganiser(con.MakeQuery(sb.ToString(), organiser));
        }

        public List<Tournament> QueryTournament(string whereClause, OrderBy order, string orderColumn)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append(SelectClause(tournament));
            sb.Append("From Tournament t");
            if (whereClause != "")
            {
                sb.Append(" Where ");
                sb.Append(whereClause);
            }
            sb.Append(OrderClause(order, orderColumn));

            return Tournament.ParseTournament(con.MakeQuery(sb.ToString(), tournament));
        }

        public List<Competition> QueryCompetition(string whereClause, OrderBy order, string orderColumn)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append(SelectClause(competition));
            sb.Append("From Tournament t, Competition cp");
            sb.Append(" Where t.tid = cp.tid");
            if (whereClause != "")
            {
                sb.Append(" AND ");
                sb.Append(whereClause);
            }
            sb.Append(OrderClause(order, orderColumn));

            return Competition.ParseCompetition(con.MakeQuery(sb.ToString(), competition));
        }

        public List<CompetesIn> QueryCompetesIn(string whereClause, OrderBy order, string orderColumn)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append(SelectClause(competesIn));
            sb.Append("From Member m, Competitor c, CompetesIn ci");
            sb.Append(" Where m.mid = c.mid AND m.mid = ci.mid");
            if (whereClause != "")
            {
                sb.Append(" AND ");
                sb.Append(whereClause);
            }
            sb.Append(OrderClause(order, orderColumn));

            return CompetesIn.ParseCompetesIn(con.MakeQuery(sb.ToString(), competesIn));
        }
       

        public List<JudgesIn> QueryJudgesIn(string whereClause, OrderBy order, string orderColumn)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append(SelectClause(judgesIn));
            sb.Append("From Member m, Judge j, JudgesIn ji");
            sb.Append(" Where m.mid = j.mid AND m.mid = ji.mid");
            if (whereClause != "")
            {
                sb.Append(" AND ");
                sb.Append(whereClause);
            }
            sb.Append(OrderClause(order, orderColumn));

            return JudgesIn.ParseJudgesIn(con.MakeQuery(sb.ToString(), judgesIn));
        }

        public List<Organises> QueryOrganises(string whereClause, OrderBy order, string orderColumn)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append(SelectClause(organises));
            sb.Append("From Member m, Organiser o, Organises oi");
            sb.Append(" Where m.mid = o.mid AND m.mid = oi.mid");
            if (whereClause != "")
            {
                sb.Append(" AND ");
                sb.Append(whereClause);
            }
            sb.Append(OrderClause(order, orderColumn));

            return Organises.ParseOrganises(con.MakeQuery(sb.ToString(), organises));
        }

        public void CreateMember(Member member)
        {
            string query = string.Format("INSERT INTO Member VALUES (memberIdSeq.nextval,{0},'{1}','{2}',);",
                 member.Name, member.Address, member.Contact);
            con.MakeQuery(query);
        }

        public void CreateCompetitor(Competitor competitor)
        {
            string query = string.Format("INSERT INTO Competitor VALUES ({5},'{0}',TO_DATE({1},'DD/MM/YYYY'),'{2}','{3}','{4}');",
                 competitor.Gender.ToString().ToLower(), competitor.BirthDate.ToShortDateString(), competitor.RepGroup, competitor.RankTitle, competitor.ArenaName,competitor.MemberID);
            con.MakeQuery(query);
        }

        public void CreateJudge(Judge judge)
        {
            string query = string.Format("INSERT INTO Judge VALUES ({1},'{0}'",
               judge.Qualification, judge.MemberID);
                 con.MakeQuery(query);

        }

        public void CreateOrganiser(Organiser organiser)
        {
            string query = string.Format("INSERT INTO Organiser VALUES (memberIdSeq.currval,'{0}'", organiser.Authority);
            con.MakeQuery(query);
        }

        public void CreateTournament(Tournament tournament)
        {
            string query = string.Format("INSERT INTO Tournament VALUES (tournamentIdSeq.nextval,TO_DATE({0},'DD/MM/YYYY'),TO_DATE({1},'DD/MM/YYYY'),'{2}','{3}');",
               tournament.StartDate.ToShortDateString(), tournament.EndDate.ToShortDateString(), tournament.Title, tournament.Address);
            con.MakeQuery(query);
        }

        public void CreateCompetition(Competition competititon)
        {
            string query = string.Format("INSERT INTO Competition VALUES ({0},'{1}',TO_DATE({2},'DD/MM/YYYY'),TO_DATE({3},'DD/MM/YYYY'),'{4}',TO_DATE({5},'DD/MM/YYYY'));",
              competititon.TournamentID, competititon.Type, competititon.MinBirthDate.ToShortDateString(), 
              competititon.MaxBirthDate.ToShortDateString(), competititon.Gender.ToString().ToLower(), 
              competititon.StartDateTime.ToShortDateString());
            con.MakeQuery(query);
        }

        public void CreateCompetesIn(CompetesIn competesIn, Competition competition)
        {//CompetesIn(mid, tid, type, minBirthDate, maxBirthDate, gender, placing, details)
            string query = string.Format("INSERT INTO Tournament VALUES ({0},{1},'{2}',TO_DATE({3},'DD/MM/YYYY'),TO_DATE({4},'DD/MM/YYYY'),'{5}','{6}','{7}');",
               competesIn.MemberID, competition.TournamentID, competition.Type, competition.MinBirthDate.ToShortDateString(),
              competition.MaxBirthDate.ToShortDateString(), competition.Gender.ToString().ToLower(), competesIn.Placing, competesIn.Details);
            con.MakeQuery(query);
        }


        public void CreateJudgesIn(JudgesIn judgesIn, Competition competition)
        {
            string query = string.Format("INSERT INTO Tournament VALUES ({0},{1},'{2}',TO_DATE({3},'DD/MM/YYYY'),TO_DATE({4},'DD/MM/YYYY'),'{5}','{6}');",
                judgesIn.MemberID, competition.TournamentID, competition.Type, competition.MinBirthDate.ToShortDateString(),
               competition.MaxBirthDate.ToShortDateString(), competition.Gender.ToString().ToLower(), judgesIn.Role);
            con.MakeQuery(query);
        }

        public void CreateOrganises(Organises organises, Tournament tournament)
        {//Organises(mid, tid, role)
            string query = string.Format("INSERT INTO Tournament VALUES ({0},{1},'{2}');",
                organises.MemberID, tournament.TournamentID, organises.Role);
            con.MakeQuery(query);
        }

        public void UpdateMember(Member member)
        {
            string query = string.Format("UPDATE Member Set name = '{1}' AND address = '{2}' AND contactDetails = '{3}' WHERE mid = {0};",
                member.MemberID, member.Name, member.Address, member.Contact);
            con.MakeQuery(query);
        }

        public void UpdateCompetitor(Competitor competitor)
        {
             string query = string.Format("UPDATE Competitor SET gender = '{1}' AND birthDate = TO_DATE({2},'DD/MM/YYYY') AND repGroup = '{3}' AND rankTitle = '{4}' AND arenaName = '{5}' WHERE mid = {0};"
                 , competitor.MemberID, competitor.Gender, competitor.BirthDate.ToShortDateString(), competitor.RepGroup, competitor.RankTitle, competitor.ArenaName);
                 con.MakeQuery(query);
        }

        public void UpdateJudge(Judge judge)
        {
            string query = string.Format("UPDATE Judge SET qualification = '{1}' WHERE mid = {0};"
                 , judge.MemberID, judge.Qualification);
            con.MakeQuery(query);
        }

        public void UpdateOrganiser(Organiser organiser)
        {
            string query = string.Format("UPDATE Organiser SET authority = '{1}' WHERE mid = {0};"
                  , organiser.MemberID, organiser.Authority);
            con.MakeQuery(query);
        }

        public void UpdateTournament(Tournament tournament)
        {
            string query = string.Format("UPDATE Tournament SET startDate =  = TO_DATE({1},'DD/MM/YYYY') AND endDate = TO_DATE({2},'DD/MM/YYYY') AND title = '{3}' AND address = '{4}' WHERE tid = {0};"
                 , tournament.TournamentID, tournament.StartDate.ToShortDateString(), tournament.EndDate.ToShortDateString(), tournament.Title, tournament.Address);
            con.MakeQuery(query);
        }

        public void UpdateCompetition(Competition competititon)
        {
            string query = string.Format("UPDATE Tournament SET startDateTime = TO_DATE({5},'DD/MM/YYYY')' WHERE tid = {0} AND type = '{1}' AND minBirthDate = TO_DATE({2},'DD/MM/YYYY') AND maxBirthDate = TO_DATE({3},'DD/MM/YYYY') AND gender = '{4}';"
                ,competititon.TournamentID, competititon.Type, competititon.MinBirthDate.ToShortDateString(), 
              competititon.MaxBirthDate.ToShortDateString(), competititon.Gender.ToString().ToLower(), 
              competititon.StartDateTime.ToShortDateString());
            con.MakeQuery(query);
        }

        public void UpdateCompetesIn(CompetesIn competesIn, Competition competition)
        {
            string query = string.Format("UPDATE CompetesIn SET placing = '{6}' AND details = '{7}' WHERE mid = {0} AND tid = {1} AND type = '{2}' AND minBirthDate = TO_DATE({3},'DD/MM/YYYY') AND maxBirthDate = TO_DATE({4},'DD/MM/YYYY') AND gender = '{5}';"
                ,competesIn.MemberID, competition.TournamentID, competition.Type, competition.MinBirthDate.ToShortDateString(),
              competition.MaxBirthDate.ToShortDateString(), competition.Gender.ToString().ToLower(), competesIn.Placing, competesIn.Details);
            con.MakeQuery(query);
        }

        public void UpdateJudgesIn(JudgesIn judgesIn, Competition competition)
        {
            string query = string.Format("UPDATE JudgesIn SET role = '{6}' WHERE mid = {0} AND tid = {1} AND type = '{2}' AND minBirthDate = TO_DATE({3},'DD/MM/YYYY') AND maxBirthDate = TO_DATE({4},'DD/MM/YYYY') AND gender = '{5}';"
                , judgesIn.MemberID, competition.TournamentID, competition.Type, competition.MinBirthDate.ToShortDateString(),
              competition.MaxBirthDate.ToShortDateString(), competition.Gender.ToString().ToLower(), judgesIn.Role);
            con.MakeQuery(query);
        }

        public void UpdateOrganises(Organises organises, Tournament tournament)
        {
            string query = string.Format("UPDATE JudgesIn SET role = '{2}' WHERE mid = {0} AND tid = {1};"
                , organises.MemberID, tournament.TournamentID, organises.Role);
            con.MakeQuery(query);
        }

        public void DeleteMember(Member member)
        {
            string query = string.Format("DELETE FROM Member m WHERE m.mid = {0} AND m.name = '{1}' AND m.address = '{2}' AND m.contactDetails = '{3}';"
                , member.MemberID, member.Name, member.Address, member.Contact);
            con.MakeQuery(query);
        }

        public void DeleteCompetitor(Competitor competitor)
        {
            string query = string.Format("DELETE FROM Competitor c WHERE c.mid = {0} AND c.gender = '{1}' AND c.birthDate = TO_DATE({2},'DD/MM/YYYY') AND c.repGroup = '{3}' AND c.rankTitle = '{4}' AND c.arenaName = '{5}';"
               , competitor.MemberID, competitor.Gender, competitor.BirthDate.ToShortDateString(), competitor.RepGroup, competitor.RankTitle, competitor.ArenaName);
            con.MakeQuery(query);
        }

        public void DeleteJudge(Judge judge)
        {
            string query = string.Format("DELETE FROM Judge j WHERE j.mid = {0} AND j.qualification = '{1}';"
               , judge.MemberID, judge.Qualification);
            con.MakeQuery(query);
        }

        public void DeleteOrganiser(Organiser organiser)
        {
            string query = string.Format("DELETE FROM Organiser o WHERE o.mid = {0} AND o.authority = '{1}';"
               , organiser.MemberID, organiser.Authority);
            con.MakeQuery(query);
        }

        public void DeleteTournament(Tournament tournament)
        {
            string query = string.Format("DELETE FROM Tournament t WHERE t.tid = {0} AND t.startDate =  = TO_DATE({1},'DD/MM/YYYY') AND c.endDate = TO_DATE({2},'DD/MM/YYYY') AND c.title = '{3}' AND c.address = '{4}';"
               , tournament.TournamentID, tournament.StartDate.ToShortDateString(), tournament.EndDate.ToShortDateString(), tournament.Title, tournament.Address);
            con.MakeQuery(query);
        }

        public void DeleteCompetition(Competition competititon)
        {
            string query = string.Format("DELETE FROM Competition cp WHERE cp.tid = {0} AND cp.type = '{1}' AND cp.minBirthDate = TO_DATE({2},'DD/MM/YYYY') AND cp.maxBirthDate = TO_DATE({3},'DD/MM/YYYY') AND cp.gender = '{4}' AND cp.startDateTime = TO_DATE({5},'DD/MM/YYYY');",
                             competititon.TournamentID, competititon.Type, competititon.MinBirthDate.ToShortDateString(), 
              competititon.MaxBirthDate.ToShortDateString(), competititon.Gender.ToString().ToLower(), 
              competititon.StartDateTime.ToShortDateString());
            con.MakeQuery(query);
        }

        public void DeleteCompetesIn(CompetesIn competesIn, Competition competition)
        {
            string query = string.Format("DELETE FROM CompetesIn ci WHERE ci.mid = {0} AND ci.tid = {1} AND ci.type = '{2}' AND ci.minBirthDate = TO_DATE({3},'DD/MM/YYYY') AND ci.maxBirthDate = TO_DATE({4},'DD/MM/YYYY') AND ci.gender = '{5}' AND ci.placing = '{6}' AND ci.details = '{7}')';",
                            competesIn.MemberID, competition.TournamentID, competition.Type, competition.MinBirthDate.ToShortDateString(),
              competition.MaxBirthDate.ToShortDateString(), competition.Gender.ToString().ToLower(), competesIn.Placing, competesIn.Details);
            con.MakeQuery(query);
        }

        public void DeleteJudgesIn(JudgesIn judgesIn, Competition competition)
        {
            string query = string.Format("DELETE FROM JudgesIn ji WHERE ji.mid = {0} AND ji.tid = {1} AND ji.type = '{2}' AND ji.minBirthDate = TO_DATE({3},'DD/MM/YYYY') AND ji.maxBirthDate = TO_DATE({4},'DD/MM/YYYY') AND ji.gender = '{5}' AND ji.role = '{6}');",
                            judgesIn.MemberID, competition.TournamentID, competition.Type, competition.MinBirthDate.ToShortDateString(),
              competition.MaxBirthDate.ToShortDateString(), competition.Gender.ToString().ToLower(), judgesIn.Role);
            con.MakeQuery(query);
        }

        public void DeleteOrganises(Organises organises, Tournament tournament)
        {
            string query = string.Format("DELETE FROM Organises oi WHERE oi.mid = {0} AND oi.tid = {1} AND oi.role = '{2}');",
                            organises.MemberID, tournament.TournamentID, organises.Role);
            con.MakeQuery(query);
        }

        public void StartTransaction()
        {
            throw new NotImplementedException();
        }

        public void CommitTransaction()
        {
            throw new NotImplementedException();
        }
    }
}
